﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using MockPrjTeam2.Entity;
using MockPrjTeam2.Common;

namespace MockPrjTeam2.DAO
{
    public class ContactDao
    {       

        public List<Contact> getAllContactByKey(string fname, string sname,bool active)
        {            
            const string getContactQuerybyKey = @"SELECT ContactId,(FirstName +' '+SurName)AS ContactName,MobilePhone,Email,ContactType,IsActive FROM Contact
                                            WHERE Contact.FirstName like  '%'+@FirstName+'%' AND Contact.SurName like '%'+@SurName+'%' AND Contact.IsActive = @IsActive";

            var listContact = new List<Contact>();
            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter { ParameterName = "@FirstName", Value = fname };
            sqlParameters[1] = new SqlParameter { ParameterName = "@SurName", Value = sname };
            sqlParameters[2] = new SqlParameter { ParameterName = "@IsActive", Value = active };
            using (var helper = new SqlHelper())
            {
                var reader = helper.ExecuteReader(getContactQuerybyKey, sqlParameters);
                while (reader.Read())
                {
                    var contact = new Contact(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetBoolean(5));
                    listContact.Add(contact);
                }

            }
            return listContact;
        
        }
        public bool addContact(Contact contact)
        {
            bool reply = false;
            const string insertContactQuery = @"INSERT INTO Contact (ManagerID,FirstName,Surname,KnowAs,OfficePhone,MobilePhone,STHomePhone,Email,ContactType,JobRole,WorkBase,JobTitle,IsActive) 
                                                        VALUES (@ManagerID, @FirstName, @Surname, @KnowAs,@OfficePhone,@MobilePhone,@STHomePhone,@Email,@ContactType,@JobRole,@WorkBase,@JobTitle,@IsActive)";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[13];
                sqlParameters[0] = new SqlParameter { ParameterName = "@ManagerID", Value = contact.ManagerId };
                sqlParameters[1] = new SqlParameter { ParameterName = "@FirstName", Value = contact.FirstName };
                sqlParameters[2] = new SqlParameter { ParameterName = "@Surname", Value = contact.SurName };
                sqlParameters[3] = new SqlParameter { ParameterName = "@KnowAs", Value = contact.KnowAs };
                sqlParameters[4] = new SqlParameter { ParameterName = "@OfficePhone", Value = contact.OfficePhone };
                sqlParameters[5] = new SqlParameter { ParameterName = "@MobilePhone", Value = contact.MobilePhone };
                sqlParameters[6] = new SqlParameter { ParameterName = "@STHomePhone", Value = contact.STHomePhone };
                sqlParameters[7] = new SqlParameter { ParameterName = "@Email", Value = contact.Email };
                sqlParameters[8] = new SqlParameter { ParameterName = "@ContactType", Value = contact.ContactType };
                sqlParameters[9] = new SqlParameter { ParameterName = "@JobRole", Value = contact.JobRole };
                sqlParameters[10] = new SqlParameter { ParameterName = "@WorkBase", Value = contact.WorkBase };
                sqlParameters[11] = new SqlParameter { ParameterName = "@JobTitle", Value = contact.JobTitle };
                sqlParameters[12] = new SqlParameter { ParameterName = "@IsActive", Value = contact.IsActive };

                reply = helper.ExecuteInsertQuery(insertContactQuery, sqlParameters);
            }

            return reply;
        }

        public bool updateContact(Contact contact)
        {
            bool reply = false;
            const string uppdateContactQuery = @"UPDATE Contact SET ManagerId = @ManagerId,
                                                                    FirstName = @FirstName,
                                                                    Surname = @Surname,
                                                                    KnowAs = @KnowAs,
                                                                    OfficePhone = @OfficePhone,
                                                                    MobilePhone = @MobilePhone,
                                                                    STHomePhone = @STHomePhone,
                                                                    Email = @Email,
                                                                    ContactType = @ContactType,
                                                                    JobRole = @JobRole,
                                                                    WorkBase = @WorkBase,
                                                                    JobTitle = @JobTitle,
                                                                    IsActive = @IsActive                                                                                                                            
                                                         WHERE ContactId=@ContactId";                                                                
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[14];
                sqlParameters[0] = new SqlParameter { ParameterName = "@ContactId", Value = contact.ContactId };
                sqlParameters[1] = new SqlParameter { ParameterName = "@ManagerId", Value = contact.ManagerId };
                sqlParameters[2] = new SqlParameter { ParameterName = "@FirstName", Value = contact.FirstName };
                sqlParameters[3] = new SqlParameter { ParameterName = "@Surname", Value = contact.SurName };
                sqlParameters[4] = new SqlParameter { ParameterName = "@KnowAs", Value = contact.KnowAs };
                sqlParameters[5] = new SqlParameter { ParameterName = "@OfficePhone", Value = contact.OfficePhone };
                sqlParameters[6] = new SqlParameter { ParameterName = "@MobilePhone", Value = contact.MobilePhone };
                sqlParameters[7] = new SqlParameter { ParameterName = "@STHomePhone", Value = contact.STHomePhone };
                sqlParameters[8] = new SqlParameter { ParameterName = "@Email", Value = contact.Email };
                sqlParameters[9] = new SqlParameter { ParameterName = "@ContactType", Value = contact.ContactType };
                sqlParameters[10] = new SqlParameter { ParameterName = "@JobRole", Value = contact.JobRole };
                sqlParameters[11] = new SqlParameter { ParameterName = "@WorkBase", Value = contact.WorkBase };
                sqlParameters[12] = new SqlParameter { ParameterName = "@JobTitle", Value = contact.JobTitle };
                sqlParameters[13] = new SqlParameter { ParameterName = "@IsActive", Value = contact.IsActive };

                reply = helper.ExecuteInsertQuery(uppdateContactQuery, sqlParameters);
            }

            return reply;
        }
        public bool updateIsActive(int id,bool active)
        {
            bool reply = false;
            const string uppdateContactQuery = @"UPDATE Contact SET IsActive = @IsActive                                                                                                                                                                                                                                                                 
                                                         WHERE ContactId=@ContactId";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[2];
                sqlParameters[0] = new SqlParameter { ParameterName = "@ContactId", Value = id };
                sqlParameters[1] = new SqlParameter { ParameterName = "@IsActive", Value = active };                                

                reply = helper.ExecuteInsertQuery(uppdateContactQuery, sqlParameters);
            }

            return reply;
        }

        public List<Contact> getContactType()
        {
            const string getContactTypeQuery = @"SELECT ContactId,ContactType FROM Contact";

            var listContact = new List<Contact>();
            
            using (var helper = new SqlHelper())
            {
                var reader = helper.ExecuteReader(getContactTypeQuery, null);
                while (reader.Read())
                {
                    var contact = new Contact(reader.GetInt32(0), reader.GetString(1));
                    listContact.Add(contact);
                }

            }
            return listContact;
        }

        public Contact getById(int id)
        {
            Contact contact;
            const string getAddbyId = @"select ContactId,ManagerId,FirstName,SurName FROM Contact Where ContactId=@ContactId";
            var sqlParameter = new SqlParameter[1];
             sqlParameter[0] = new SqlParameter { ParameterName = "@ContactId", Value = id };
            using (var helper = new SqlHelper())
            {
                var reader = helper.ExecuteReader(getAddbyId, sqlParameter);
                if (reader.Read())
                {
                    contact = new Contact(reader.GetInt32(0),reader.GetInt32(1), reader.GetString(2),reader.GetString(3));
                }
                else
                    contact = null;
            }
            return contact;
        }
        public Contact getContactDetail(int id)
        {
            Contact contact;
            const string getAddbyId = @"select ContactId,ManagerId,(FirstName +' '+SurName)AS ContactName,FirstName,Surname,KnowAs,OfficePhone,MobilePhone,STHomePhone,Email,ContactType,JobRole,WorkBase,JobTitle,IsActive
                                        FROM Contact Where ContactId=@ContactId";
            var sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter { ParameterName = "@ContactId", Value = id };
            using (var helper = new SqlHelper())
            {
                var reader = helper.ExecuteReader(getAddbyId, sqlParameter);
                if (reader.Read())
                {
                    contact = new Contact(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2),reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetString(8), reader.GetString(9),
                        reader.GetString(10), reader.GetString(11), reader.GetString(12), reader.GetString(13), reader.GetBoolean(14));
                }
                else
                    contact = null;
            }
            return contact;
        }
    }
}